use mtdb;
GO

IF NOT EXISTS (SELECT 'X' FROM SYS.FILEGROUPS WHERE NAME='FG_MTDB_0') ALTER DATABASE mtdb ADD FILEGROUP FG_MTDB_0;
IF NOT EXISTS (SELECT 'X' FROM SYS.FILEGROUPS WHERE NAME='FG_MTDB_1') ALTER DATABASE mtdb ADD FILEGROUP FG_MTDB_1;
GO

DECLARE @DataFilePath varchar(256);
DECLARE @DataFileName varchar(256);
DECLARE @Query varchar(512);

SELECT
	@DataFilePath = (LEFT(PHYSICAL_NAME, LEN(PHYSICAL_NAME) - CHARINDEX('\', REVERSE(PHYSICAL_NAME) + '\')))
FROM
	(SELECT PHYSICAL_NAME FROM SYS.DATABASE_FILES WHERE NAME='mtdb') AS PHYSICAL_NAME;

SET @DataFileName = '''' + @DataFilePath + '\mtdb_0.ndf' + '''';
SET @Query = 'ALTER DATABASE mtdb ADD FILE ('
    + 'NAME = mtdb_0,'
    + 'FILENAME = ' + @DataFileName + ','
    + 'SIZE = 5MB,'
    + 'FILEGROWTH = 1MB)'
	+ 'TO FILEGROUP FG_MTDB_0'
IF NOT EXISTS (SELECT 'X' FROM SYS.DATABASE_FILES WHERE NAME='mtdb_0') EXEC(@Query);
SET @DataFileName = '''' + @DataFilePath + '\mtdb_1.ndf' + '''';
SET @Query = 'ALTER DATABASE mtdb ADD FILE ('
    + 'NAME = mtdb_1,'
    + 'FILENAME = ' + @DataFileName + ','
    + 'SIZE = 5MB,'
    + 'FILEGROWTH = 1MB)'
	+ 'TO FILEGROUP FG_MTDB_1'
IF NOT EXISTS (SELECT 'X' FROM SYS.DATABASE_FILES WHERE NAME='mtdb_1') EXEC(@Query);
GO

IF NOT EXISTS (SELECT 'X' FROM SYS.PARTITION_FUNCTIONS WHERE NAME='PF_MTDB')
CREATE PARTITION FUNCTION PF_MTDB (NUMERIC(7,0)) AS RANGE RIGHT FOR VALUES (1);
GO

IF NOT EXISTS (SELECT 'X' FROM SYS.PARTITION_SCHEMES WHERE NAME='PS_MTDB')
CREATE PARTITION SCHEME PS_MTDB AS PARTITION PF_MTDB TO (FG_MTDB_0, FG_MTDB_1);
GO
